Coding Schools

Normalization in SQL Server
DDL and DML in SQL Server
SQL Server System Databases
SQL Server System Tables
Joins in SQL Server
SQL Server - Indexes
CTE in SQL Server
SQL Server - Locks
Optimization Techniques in SQL Server
How to Setup NDF Files in SQL Server
Deadlocks in SQL Server
Transactions in SQL Server
Difference UDF vs Stored Procedure
SQL Server Triggers
SQL Server Isolation Levels
SQL Server Pivot Tables
SQL Server - Group By Statement
SQL Server - Find Second highest Number Query
SQL Server - Recursion in User Defined Function
SQL Server Error Handling
Difference Table Variable and Temporary Table
SQL Server - Temporal Tables
User Defined Functions - SQL Server
Stored Procedures - SQL Server

SQL Server Error Handling

Error handling in SQL Server is crucial for managing exceptions and ensuring that transactions are handled gracefully. Here are the main techniques and approaches for error handling in SQL Server:

Using TRY...CATCH Block

The TRY...CATCH block is used to handle exceptions in SQL Server. The code within the TRY block is executed, and if an error occurs, control is passed to the CATCH block.

Example

sql

BEGIN TRY
    -- Your SQL code here
    BEGIN TRANSACTION;

    -- Example operation
    INSERT INTO Employees (EmployeeID, FirstName, LastName)
    VALUES (1, 'John', 'Doe');

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Handle the error
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    DECLARE @ErrorMessage NVARCHAR(4000);
    SELECT @ErrorMessage = ERROR_MESSAGE();

    -- Log the error message or take other actions
    PRINT 'Error occurred: ' + @ErrorMessage;
END CATCH;

Functions for Error Handling

Within the CATCH block, you can use several functions to get detailed information about the error:

ERROR_MESSAGE(): Returns the error message.
ERROR_NUMBER(): Returns the error number.
ERROR_SEVERITY(): Returns the error severity.
ERROR_STATE(): Returns the error state.
ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE(): Returns the line number where the error occurred.

Example of Detailed Error Information

sql

BEGIN TRY
    -- Example operation
    DELETE FROM Employees WHERE EmployeeID = 10;
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000),
            @ErrorNumber INT,
            @ErrorSeverity INT,
            @ErrorState INT,
            @ErrorProcedure NVARCHAR(200),
            @ErrorLine INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorNumber = ERROR_NUMBER(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE(),
           @ErrorProcedure = ERROR_PROCEDURE(),
           @ErrorLine = ERROR_LINE();

    PRINT 'Error Details:';
    PRINT 'Message: ' + @ErrorMessage;
    PRINT 'Number: ' + CAST(@ErrorNumber AS NVARCHAR);
    PRINT 'Severity: ' + CAST(@ErrorSeverity AS NVARCHAR);
    PRINT 'State: ' + CAST(@ErrorState AS NVARCHAR);
    PRINT 'Procedure: ' + ISNULL(@ErrorProcedure, 'N/A');
    PRINT 'Line: ' + CAST(@ErrorLine AS NVARCHAR);
END CATCH;

Nested TRY...CATCH Blocks

You can nest TRY...CATCH blocks to handle errors at different levels of your SQL code.

Example

sql

BEGIN TRY
    BEGIN TRANSACTION;

    BEGIN TRY
        -- Example operation that might fail
        UPDATE Employees SET Salary = Salary * 1.1 WHERE EmployeeID = 1;
    END TRY
    BEGIN CATCH
        -- Handle inner error
        PRINT 'Inner Error: ' + ERROR_MESSAGE();
    END CATCH;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Handle outer error
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    PRINT 'Outer Error: ' + ERROR_MESSAGE();
END CATCH;

Best Practices

Transaction Handling: Always ensure that transactions are properly committed or rolled back.
Logging: Log error details to a table or external system for later analysis.
Retry Logic: Implement retry logic for transient errors, especially in applications.
Validation: Perform data validation to catch potential errors before executing SQL statements.

Proper error handling helps in maintaining the stability and reliability of your SQL Server applications. If you have specific scenarios or need further examples, feel free to let me know!